package ta.utils;

import com.alibaba.druid.pool.DruidDataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class JDBCUtils {
	
	private static QueryRunner run = new QueryRunner();
	private static DruidDataSource ds = null;

	// 只放进行事务的 Connection
	private static ThreadLocal<Connection> conn = new ThreadLocal<>();

	static {
		ds = (DruidDataSource) DruidConnection.getDataSource();
	}

	/**
	 * 通过 DruidDataSource 得到 Connection
	 * 
	 * @return Connection 对象
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		// 得到 ThreadLocal 中的 Connection
		Connection con = conn.get();
		// 如果开启了事务，则con不为空，应该直接返回con
		if (null == con || con.isClosed()) {
			con = ds.getConnection();
			conn.set(con);
		}
		return con;
	}

	// ---------------------------对事物操作的封装-------------------------------------

	/**
	 * 开启事务
	 * 
	 * @throws SQLException
	 */
	public static void beginTransaction() throws SQLException {
		// 得到ThreadLocal中的connection
		Connection con = conn.get();
		// 设置事务提交为手动
		con.setAutoCommit(false);
		// 把当前开启的事务放入ThreadLocal中
		conn.set(con);
	}

	/**
	 * 提交事务
	 * 
	 * @throws SQLException
	 */
	public static void commitTransaction() throws SQLException {
		// 得到ThreadLocal中的connection
		Connection con = conn.get();
		// 判断con是否为空，如果为空，则说明没有开启事务
		if (con == null) {
			throw new SQLException("没有开启事务,不能提交事务");
		}
		// 如果 con 不为空,提交事务
		con.commit();
		// 事务提交后，关闭连接
		con.close();
		// ThreadLocal中移出连接
		conn.remove();
	}

	/**
	 * 回滚事务
	 */
	public static void rollbackTransaction() {
		try {
			// 得到 ThreadLocal 中的 connection
			Connection con = conn.get();
			// 判断con是否为空，如果为空，则说明没有开启事务，不能回滚事务
			if (con == null) {
				throw new SQLException("没有开启事务,不能回滚事务");
			}
			// 事务回滚
			con.rollback();
			// 关闭连接
			con.close();
			// 将连接移除 ThreadLocal
			conn.remove();
		} catch (SQLException e) {
			System.err.println("回滚事务失败..." + e.getMessage());
		}
	}

	/**
	 * 关闭事务
	 * 
	 * @param connection
	 * @throws SQLException
	 */
	public static void releaseConnection(Connection connection) {
		// 得到ThreadLocal中的connection
		Connection con = conn.get();
		// 如果参数连接与当前事务连接不相等，则说明参数连接不是事务连接，可以关闭，否则交由事务关闭
		if (connection != null && con != connection) {
			try {
				// 如果连接没有被关闭，关闭之
				if (!connection.isClosed()) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 关闭 DruidDataSource
	 */
	public static void closeDataSource() {
		if (null != ds) {
			ds.close();
		}
	}

	// -----------------------重写 QueryRunner 中的方法--------------------------

	
	public static <T> List<T> getList(String sql, Class<T> clazz) {
		return getList(sql, new Object[0], clazz);
	}

	public static <T> List<T> getList(String sql, Object[] params,  Class<T> clazz) {
		BeanListHandler<T>  handler=new BeanListHandler<T>(clazz);
		List<T> result = new ArrayList<>();
		try {
			Connection con = getConnection();
			result = run.query(con, sql, handler, params);
			releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public static <T> T getBean(String sql, Class<T> clazz) {		
		return getBean( sql, new Object[0],clazz);
	}


	public static <T> T getBean(String sql, Object[] params, Class<T> clazz) {
		BeanHandler<T> handler=new BeanHandler<T>(clazz);
		T result = null;
		try {
			Connection con = getConnection();
			result = run.query(con, sql, handler, params);
			releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static boolean update(String sql, Object[] params) {
		int result = -1;
		Connection con = null;
		try {
			con = getConnection();
			result = run.update(con, sql, params);
		} catch (SQLException e) {
			// TODO: handle exception
		} finally {
			releaseConnection(con);
		}
		return result > 0;
	}

	public static boolean update(String sql) {

		return update(sql, new Object[0]);
	}
	
	public static Object getScalar(String sql) {	
		Connection con = null;
		Object object=null;
		try {
			con = getConnection();
			object = run.query(con,sql, new ScalarHandler<Object>());
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		finally {
			releaseConnection(con);
		}
		return object;
	}
	
	public static Object getScalar(String sql,Object[] paras) {	
		Connection con = null;
		Object object=null;
		try {
			con = getConnection();
			object = run.query(con,sql, new ScalarHandler<Object>(),paras);
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		finally {
			releaseConnection(con);
		}
		return object;
	}
	
	public static Object[] getSingleRow(String sql,Object[] paras) {		
		Connection con = null;
		Object[] object=null;
		try {
			con = getConnection();
			object = run.query(con,sql, new ArrayHandler(),paras);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			releaseConnection(con);
		}
		return object;
	}
	
	public static Object[] getSingleRow(String sql) {		
		
		return getSingleRow(sql,new Object[0]);
	}
	
	public static List<Object> getSingleCol(String sql,Object[] paras) {		
		Connection con = null;
	   List<Object> list=new ArrayList<Object>();
		try {
			con = getConnection();
			list = run.query(con,sql, new ColumnListHandler<Object>(),paras);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			releaseConnection(con);
		}
		return list;
	}
	
	public static List<Object> getSingleCol(String sql) {		
		
		return getSingleCol(sql,new Object[0]);
	}
	
	
	public static List<Object[]> getListArray(String sql) {		
		
		return getListArray(sql,new Object[0]);
	}
	
	public static List<Object[]> getListArray(String sql,Object[] paras) {		
		Connection con = null;
		List<Object[]>  list=new ArrayList<Object[]>();
		try {
			con = getConnection();
			list = run.query(con,sql, new ArrayListHandler(),paras);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			releaseConnection(con);
		}
		return list;
	}
	
	public static void transfer(String[] sqllist, Object[][] params) {
		Connection con = null;
		try {
			con = getConnection();
			beginTransaction();
			for (int i = 0; i < params.length; i++) {
				run.update(con, sqllist[i], params[i]);
			}
			commitTransaction();
		} catch (SQLException e) {
			rollbackTransaction();
		} finally {
			releaseConnection(con);
		}

	}
	
	public static List<String> getColunmList(String sql)
	{
		Connection con = null;
		List<String> list=new ArrayList<String>();
		ColumnListHandler<String> handler=new ColumnListHandler<String>();
		try {
			con = getConnection();
			list = run.query(con,sql,handler);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			releaseConnection(con);
		}
		return list;
	}

	
	public static void main(String[] args) {
		String sql = "select column_name ,data_type,column_comment from information_schema.columns where table_name =  'userinfo' ";
        List<Object[]> list = JDBCUtils.getListArray(sql);  
     
        for(Object[] objs:list)
        {
        	for(Object obj:objs)
        	{
        		System.out.print(obj+" ");
        	}
        	System.out.println();
        }
        
	}
	
}
